/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package BD.Modulos;

import BD.Bd;
import BD.ConnBdType;
import Interfaces.BdException;
import Interfaces.GuideInterface;
import Objects.Log_CGuias;
import Objects.Log_CGuias_Falt;
import Objects.Log_CGuias_Perm;
import WS.Guias;
import WS.Respuestas.RespBdGuias;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author armgarces
 */
public class BdGuias extends Bd{
    private String[] params = null;
    /***************************************************************************/
    /********************************** BD *************************************/
    /***************************************************************************/
    /**
     * 
     */
    public BdGuias(){
       
    }  
    /**
     * 
     * @param value
     * @return 
     */
    private String selectFindSP(GuideInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Archguid":
                params = new String[]{"@numguia","@producto" };
                return "{call sp_get_archguid_find(?,?)}";
            case "Archguip_Det":
                params = new String[]{"@numguia","@almacen" };
                return "{call sp_get_archguip_find_det(?, ?)}";
            case "Archguip_Pro":
                params = new String[]{"@numguia","@producto" };
                return "{call sp_get_archguip_find_pro(?, ?)}";
            case "Archguip_Sum":
                params = new String[]{"@numguia","@almacen" };
                return "{call sp_get_archguip_find_sum(?, ?)}";               
            default:
                return "";
        }
    }
    /**
     * 
     * @param gi
     * @return 
     */
    private String selectCheckSP(int value){
        switch(value){
            case Guias.CHECK_ARCHGUIE:
                params = new String[]{"@numguia"};
                return "{call sp_get_archguie_check(?,?)}";
            case Guias.CHECK_ARCHGUIP:
                params = new String[]{"@numguia","@producto"};
                return "{call sp_get_archguip_check(?,?,?)}";
            case Guias.CHECK_CGUIDE:
                params = new String[]{"@numrela"};
                return "{call sp_get_log_cguias_find(?)}";              
            default:
                return "";
        }
    }
    /***************************************************************************/
    /********************************* GUIDE ***********************************/
    /***************************************************************************/
    /**
     * 
     * @param gi
     * @param find
     * @param value
     * @return 
     * @throws Interfaces.BdException 
     * @throws java.sql.SQLException 
     */
    public GuideInterface[] find(GuideInterface gi, String find, String value) throws BdException, SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();                                             
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectFindSP(gi));            
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                
                    cstmt.setString(params[0] , find );
                    cstmt.setString(params[1] , value );
                ResultSet result = cstmt.executeQuery();                           
                List<GuideInterface> list = new ArrayList<>();
                while(result.next()) {
                   GuideInterface obj = gi.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new GuideInterface[list.size()]);
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param check
     * @param guia
     * @param value
     * @return
     * @throws SQLException 
     * @throws Interfaces.BdException 
     */
    public boolean check(int check,String guia,String value) throws SQLException, BdException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();                       
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectCheckSP(check)); 
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
                cstmt.setString(params[0], guia);
                //
                if(check == Guias.CHECK_ARCHGUIP)
                    cstmt.setString(params[1], value);
                cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
                cstmt.execute();
                if(cstmt.getInt("result") == 1) return true;               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param log_cguias
     * @param pos
     * @return
     * @throws Interfaces.BdException
     * @throws SQLException 
     */
    public RespBdGuias save_CGuias(String user, Log_CGuias log_cguias, int operacion, int pos) throws BdException, SQLException {
        RespBdGuias resp = new RespBdGuias();              
        try{
         Bd bd = ConnBdType.open(ConnBdType.SqlServer);
         connection = bd.open();  
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql_rela = new StringBuilder();            
            StringBuilder sql_data = new StringBuilder();            
            StringBuilder sql_chof = new StringBuilder();            
            StringBuilder sql_vehi = new StringBuilder();            
            StringBuilder sql_ayud = new StringBuilder();            
            StringBuilder sql_cheq = new StringBuilder();            
            StringBuilder sql_perm = new StringBuilder();            
            StringBuilder sql3 = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql_rela.append("{call sp_ins_log_cguias_numrela(?)}");
                    sql_data.append("{call sp_ins_log_cguias_data(?, ?, ?, ?, ?)}");
                    sql_chof.append("{call sp_ins_log_cguias_chof(?, ?, ?)}");
                    sql_vehi.append("{call sp_ins_log_cguias_vehi(?, ?, ?, ?)}");
                    sql_ayud.append("{call sp_ins_log_cguias_ayud(?, ?, ?, ?)}");
                    sql_cheq.append("{call sp_ins_log_cguias_cheq(?, ?, ?, ?)}");
                    sql_perm.append("{call sp_ins_log_cguias_cheq(?, ?, ?, ?)}");
                    mensj = "Nueva Guia:";
                    break;
                case 2:
                    sql_data.append("{call sp_upd_log_personal_basic(?, ?, ?, ?)}");
                    mensj = "Actualizando Guia:";
                    break;
            }
            CallableStatement cstmt = null;
            if (pos == 0){
                /**
                 * Busqueda NumRela
                 */
                cstmt = connection.prepareCall(sql_rela.toString());
                cstmt.registerOutParameter("numrela"    , java.sql.Types.DECIMAL);
                cstmt.execute();            
                log_cguias.setNumrela(cstmt.getDouble("numrela"));
//                Datos.setNumRela(cstmt.getDouble("numrela"));
                resp.setNumRela(cstmt.getDouble("numrela"));
                /**
                 * Choferes
                 */
                cstmt = connection.prepareCall(sql_chof.toString());
                cstmt.setDouble("@numrela"         , log_cguias.getNumrela());
                if(log_cguias.getFecha().toString().equals(LocalDate.now().toString())) 
                    cstmt.setDate("@fecha"         , null);
                else
                    cstmt.setDate("@fecha"         , log_cguias.getFecha());
                cstmt.setString("@id_personal"     , log_cguias.getChofer());
                cstmt.execute();                           
                /**
                 * Vehiculos
                 */
                cstmt = connection.prepareCall(sql_vehi.toString());
                cstmt.setDouble("@numrela"         , log_cguias.getNumrela());
                if(log_cguias.getFecha().toString().equals(LocalDate.now().toString())) 
                    cstmt.setDate("@fecha"         , null);
                else
                    cstmt.setDate("@fecha"         , log_cguias.getFecha());
                cstmt.setString("@nro_placa1"      , log_cguias.getVeh1());
                cstmt.setString("@nro_placa2"      , log_cguias.getVeh2());
                cstmt.execute();            
                /**
                 * Ayudantes
                 */
                cstmt = connection.prepareCall(sql_ayud.toString());
                cstmt.setDouble("@numrela"         , log_cguias.getNumrela());
                if(log_cguias.getFecha().toString().equals(LocalDate.now().toString())) 
                    cstmt.setDate("@fecha"         , null);
                else
                    cstmt.setDate("@fecha"         , log_cguias.getFecha());
                cstmt.setString("@id_personal1"    , log_cguias.getAyud1());
                cstmt.setString("@id_personal2"    , log_cguias.getAyud2());
                cstmt.execute();            
                /**
                 * Chequeador Puerta
                 */
                cstmt = connection.prepareCall(sql_cheq.toString());
                cstmt.setDouble("@numrela"         , log_cguias.getNumrela());
                if(log_cguias.getFecha().toString().equals(LocalDate.now().toString())) 
                    cstmt.setDate("@fecha"         , null);
                else
                    cstmt.setDate("@fecha"         , log_cguias.getFecha());
                cstmt.setString("@id_personal"     , log_cguias.getCheq_pta());
                cstmt.setInt("@id_tchequeo"        , log_cguias.getTcheq_pta());
                cstmt.execute();            
                /**
                 * Sada e Insopesca
                 */
            }
            /**
             * Guias de Carga
             */
            cstmt = connection.prepareCall(sql_data.toString());
            cstmt.setDouble("@numrela"              , log_cguias.getNumrela());
            cstmt.setDouble("@numorden"             , pos + 1);
            cstmt.setString("@numguia"              , log_cguias.getNumguia());
            cstmt.setInt("@numpuerta"               , log_cguias.getNumpuerta());
            if(log_cguias.getFecha().toString().equals(LocalDate.now().toString())) 
                cstmt.setDate("@fecha"         , null);
            else
                cstmt.setDate("@fecha"         , log_cguias.getFecha());
            cstmt.execute();            
            // Auditar el proceso
            auditar(user, log_cguias.getNumrela()+ "",mensj);            
            connection.commit();     
            resp.setResult(1);
            
         }else{
            throw new BdException("Error: Connexion BD no activa");
         }
        }catch(SQLException e){            
            connection.rollback();   
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
        return resp;
    }
    /**
     * @author MITM
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2
     * UPDATE
     * @param log_cguias_perm
     * @param pos
     * @return
     * @throws SQLException
     * @throws Interfaces.BdException
     */
    public boolean save_CGuias_Perm(String user,Log_CGuias_Perm log_cguias_perm, int operacion, int pos) throws SQLException, BdException {
        try{
         Bd bd = ConnBdType.open(ConnBdType.SqlServer);
         connection = bd.open();  
            if (connection != null) {
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sqlProc = new StringBuilder();
                String mensj = "";
                switch (operacion) {
                    case 1:
                        sqlProc.append("{call sp_ins_log_cguias_perm(?, ?, ?, ?)}");
                        mensj = "Nueva Guia:";
                        break;
                    case 2:
                        sqlProc.append("{call sp_upd_log_personal_basic(?, ?, ?, ?)}");
                        mensj = "Actualizando Guia:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
                cstmt.setDouble("@numrela", log_cguias_perm.getNumrela());
//                cstmt.setDouble("@numorden", pos + 1);
                cstmt.setString("@numpermiso", log_cguias_perm.getGuias());
                cstmt.setInt("@id_tpermiso", log_cguias_perm.getTpermiso());
                if (log_cguias_perm.getFecha().toString().equals(LocalDate.now().toString())) {
                    cstmt.setDate("@fecha", null);
                } else {
                    cstmt.setDate("@fecha", log_cguias_perm.getFecha());
                }
                cstmt.execute();
                // Auditar el proceso
                auditar(user,log_cguias_perm.getGuias() + "", mensj);
                connection.commit();
                return true;
            } else {
                throw new BdException("Error: Connexion BD no activa");
            }
        } catch (SQLException e) {
            connection.rollback();
            throw new BdException("Error en BD: "+e);
        } finally {
            connection.close();
        }
    }
    /**
     * @author MITM
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2
     * UPDATE
     * @param log_cguias_falt
     * @param pos
     * @return
     * @throws SQLException
     * @throws Interfaces.BdException
     */
    public boolean save_CGuias_falt(String user,Log_CGuias_Falt log_cguias_falt, int operacion, int pos) throws SQLException, BdException {
        try {
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null) {
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sqlProc = new StringBuilder();
                String mensj = "";
                switch (operacion) {
                    case 1:
                        sqlProc.append("{call sp_ins_log_cguias_falt(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        mensj = "Nueva Guia:";
                        break;
                    case 2:
                        sqlProc.append("{call sp_upd_log_personal_basic(?, ?, ?, ?)}");
                        mensj = "Actualizando Guia:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
                cstmt.setDouble("@numrela", log_cguias_falt.getNumrela());
                cstmt.setDouble("@numorden", pos + 1);
                cstmt.setString("@numguia", log_cguias_falt.getGuias());
                cstmt.setString("@numfalt", log_cguias_falt.getFaltante());
                cstmt.setString("@producto", log_cguias_falt.getProducto());
                cstmt.setInt("@cantfact", log_cguias_falt.getCantfact());
                cstmt.setInt("@cantfalt", log_cguias_falt.getCantfalt());
                cstmt.setInt("@cantdesp", log_cguias_falt.getCantdesp());
                cstmt.setInt("@id_unidad", log_cguias_falt.getId_unidad());
                if (log_cguias_falt.getFecha().toString().equals(LocalDate.now().toString())) {
                    cstmt.setDate("@fecha", null);
                } else {
                    cstmt.setDate("@fecha", log_cguias_falt.getFecha());
                }
                cstmt.execute();
                // Auditar el proceso
                auditar(user, log_cguias_falt.getGuias() + "", mensj);
                connection.commit();
                return true;
            } else {
                throw new BdException("Error: Connexion BD no activa");
            }
        } catch (Exception e) {
            connection.rollback();
            throw new BdException("Error en BD: "+e);
        } finally {
            connection.close();
        }
    }
    /***************************************************************************/
    /******************************* SINGLETON *********************************/
    /***************************************************************************/    
    private static class BdHolder {
        private static final BdGuias INSTANCE = new BdGuias();
    }
    public static BdGuias getInstance() {
        return BdGuias.BdHolder.INSTANCE;
    }
}